package com.samebuilding.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.samebuilding.dao.UserDAO;
import com.samebuilding.domain.User;

public class UserDAOImpl extends HibernateDaoSupport implements UserDAO{

	public void save(User u) {
		this.getHibernateTemplate().save(u);
		
	}

	public void update(User u) {
		this.getHibernateTemplate().update(u);
	}

	
	public User findByEmail(String email){
		List list= this.getHibernateTemplate().find("from User as u where u.email=?",email);
		if(list!=null&&list.size()>0){
			return (User)list.get(0);
		}
		return null;

	}

	public List findVIP() { 
		String hql="from User as u where u.vip>0 order by u.vip desc";
		List list= this.getHibernateTemplate().find(hql);
		return list;
	}

	public User findByUserNo(int userNo) {
		List list= this.getHibernateTemplate().find("from User as u where u.userNo=?",userNo);
		if(list!=null&&list.size()>0){
			return (User)list.get(0);
		}
		return null;
	}
	public List findSameRegionCode(int code){
		return this.getHibernateTemplate().find("from User as u where u.regionCode=?",code);
	}

	public List findSameBuilding(int code) {
		return this.getHibernateTemplate().find("from User as u where u.buildingCode=?",code);
	}

	public boolean isVIP(int userNo) {
		List list=this.getHibernateTemplate().find("from User as u where u.userNo=?",userNo);
		if(list!=null&&list.size()>0){
			User u=(User)list.get(0);
			int vip=u.getVip();
			if(vip>0){
				return true;
			}
		}
		return false;
	}

	public List findByCon(User u, int age) {
		StringBuffer sb = new StringBuffer();
		sb.append("from User as u where ");
		sb.append("u.userNo>1");
		sb.append("and u.email like ? ");
		sb.append("and u.username like ? ");
		sb.append("and u.hobby like ? ");
		if(u.getGender()!=2){
			sb.append("and u.gender="+u.getGender());
		}
		if(u.getProvinceCode()!=0){
			sb.append("and u.provinceCode="+u.getProvinceCode());	
		}
		if(u.getCityCode()!=0){
			sb.append("and u.cityCode="+u.getCityCode());
		}
		if(u.getRegionCode()!=0){
			sb.append("and u.regionCode="+u.getRegionCode());
		}
		if(age==0){
			Query query = this.getSession().createQuery(sb.toString());
			
			query.setString(0, "%"+u.getEmail()+"%");
			query.setString(1, "%"+u.getUsername()+"%");
			query.setString(2, "%"+u.getHobby()+"%");
			return query.list();
		}
		List returnList = new ArrayList();
		if(age==1){
			Date now=new Date();
			now.setYear(now.getYear()-16);
			sb.append(" and u.birthday > ?");
			Query query = this.getSession().createQuery(sb.toString());
			
			query.setString(0, "%"+u.getEmail()+"%");
			query.setString(1, "%"+u.getUsername()+"%");
			query.setString(2, "%"+u.getHobby()+"%");
			query.setDate(3, new java.sql.Date(now.getTime()));
			return query.list();
		}
		if(age==2){
			Date now=new Date();
			now.setYear(now.getYear()-15);		 
			sb.append(" and u.birthday <= ?");
			sb.append(" and u.birthday > ?");
			Query query = this.getSession().createQuery(sb.toString());
			
			query.setString(0, "%"+u.getEmail()+"%");
			query.setString(1, "%"+u.getUsername()+"%");
			query.setString(2, "%"+u.getHobby()+"%");
			query.setDate(3, new java.sql.Date(now.getTime()));
			now.setYear(now.getYear()-8);
			query.setDate(4, new java.sql.Date(now.getTime()));
			return query.list();
		}
		if(age==3){
			Date now=new Date();
			now.setYear(now.getYear()-22);
			sb.append(" and u.birthday <='"+new java.sql.Date(now.getTime())+"'");
			
			sb.append(" and u.birthday >'"+new java.sql.Date(now.getTime())+"'");
			Query query = this.getSession().createQuery(sb.toString());
			
			query.setString(0, "%"+u.getEmail()+"%");
			query.setString(1, "%"+u.getUsername()+"%");
			query.setString(2, "%"+u.getHobby()+"%");
			query.setDate(3, new java.sql.Date(now.getTime()));
			now.setYear(now.getYear()-9);
			query.setDate(4, new java.sql.Date(now.getTime()));
			return query.list();
		}
		if(age==4){
			Date now=new Date();
			now.setYear(now.getYear()-30);
			sb.append(" and u.birthday <='"+new java.sql.Date(now.getTime())+"'");
			
			sb.append(" and u.birthday >'"+new java.sql.Date(now.getTime())+"'");
			Query query = this.getSession().createQuery(sb.toString());
			
			query.setString(0, "%"+u.getEmail()+"%");
			query.setString(1, "%"+u.getUsername()+"%");
			query.setString(2, "%"+u.getHobby()+"%");
			query.setDate(3, new java.sql.Date(now.getTime()));
			now.setYear(now.getYear()-9);
			query.setDate(4, new java.sql.Date(now.getTime()));
			return query.list();
		}
		if(age==5){
			Date now=new Date();
			now.setYear(now.getYear()-40);
			sb.append(" and u.birthday <= ?");
			Query query = this.getSession().createQuery(sb.toString());
			
			query.setString(0, "%"+u.getEmail()+"%");
			query.setString(1, "%"+u.getUsername()+"%");
			query.setString(2, "%"+u.getHobby()+"%");
			query.setDate(3, new java.sql.Date(now.getTime()));
			return query.list();
		}
		return returnList;
	}
	public void flushSession() {
		getSession().flush();
	}

	public boolean isComment(int userNo) {
		List list=this.getHibernateTemplate().find("from User as u where u.userNo=?",userNo);
		if(list!=null&&list.size()>0){
			User u=(User)list.get(0);
			return u.isAllowComment();
		}
		return false;
	}

	public boolean isLeave(int userNo) {
		List list=this.getHibernateTemplate().find("from User as u where u.userNo=?",userNo);
		if(list!=null&&list.size()>0){
			User u=(User)list.get(0);
			return u.isAllowLeave();
		}
		return false;
	}

	@SuppressWarnings("unchecked")
	public List<User> findBySimpleCondition(final User u, final int age, final int start, final int limit) {
		return (List<User>)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Criteria c = s.createCriteria(User.class);
				
				if(u.getGender() != 2) {
					c.add(Restrictions.eq("gender", u.getGender()));
				}
				
				if(age != 0) {
					Date now1 = new Date();
					Date now2 = new Date();
					switch(age) {
						case 1:
							now1.setYear(now1.getYear()-16);
							c.add(Restrictions.ge("birthday", now1));
							break;
						case 2:
							now1.setYear(now1.getYear()-15);
							now2.setYear(now1.getYear()-23);
							c.add(Restrictions.between("birthday", now1, now2));
							
							break;
						case 3:
							now1.setYear(now1.getYear()-22);
							now2.setYear(now1.getYear()-31);
							c.add(Restrictions.between("birthday", now1, now2));
						case 4:
							now1.setYear(now1.getYear()-30);
							now2.setYear(now1.getYear()-39);
							c.add(Restrictions.between("birthday", now1, now2));
							break;
						case 5:
							now1.setYear(now1.getYear()-40);
							c.add(Restrictions.le("birthday", now1));
							break;
					}
				}
				
				if(u.getProvinceCode() != 0) {
					c.add(Restrictions.eq("provinceCode", u.getProvinceCode()));
				}
				
				if(u.getCityCode() != 0) {
					c.add(Restrictions.eq("cityCode", u.getCityCode()));
				}
				
				if(u.getRegionCode() != 0) {
					c.add(Restrictions.eq("regionCode", u.getRegionCode()));
				}
				
				if(u.getCountryProCode() != 0) {
					c.add(Restrictions.eq("countryProCode", u.getCountryProCode()));
				}
				
				if(u.getCountryCityCode() != 0) {
					c.add(Restrictions.eq("countryCityCode", u.getCountryCityCode()));
				}
				
				if(u.getCountryRegCode() != 0) {
					c.add(Restrictions.eq("countryRegCode", u.getCountryRegCode()));
				}
				
				
				c.setFirstResult(start);
				c.setMaxResults(limit);
				
				List<User> list = c.list();
		        return list;
			}
		});
	}


	public int findCountBySimpleCondition(final User u, final int age) {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException, SQLException {
				Criteria c = s.createCriteria(User.class);
				c.setProjection(Projections.rowCount());
				if(u.getGender() != 2) {
					c.add(Restrictions.eq("gender", u.getGender()));
				}
				
				if(age != 0) {
					Date now1 = new Date();
					Date now2 = new Date();
					switch(age) {
						case 1:
							now1.setYear(now1.getYear()-16);
							c.add(Restrictions.ge("birthday", now1));
							break;
						case 2:
							now1.setYear(now1.getYear()-15);
							now2.setYear(now1.getYear()-23);
							c.add(Restrictions.between("birthday", now1, now2));
							
							break;
						case 3:
							now1.setYear(now1.getYear()-22);
							now2.setYear(now1.getYear()-31);
							c.add(Restrictions.between("birthday", now1, now2));
						case 4:
							now1.setYear(now1.getYear()-30);
							now2.setYear(now1.getYear()-39);
							c.add(Restrictions.between("birthday", now1, now2));
							break;
						case 5:
							now1.setYear(now1.getYear()-40);
							c.add(Restrictions.le("birthday", now1));
							break;
					}
				}
				
				if(u.getProvinceCode() != 0) {
					c.add(Restrictions.eq("provinceCode", u.getProvinceCode()));
				}
				
				if(u.getCityCode() != 0) {
					c.add(Restrictions.eq("cityCode", u.getCityCode()));
				}
				
				if(u.getRegionCode() != 0) {
					c.add(Restrictions.eq("regionCode", u.getRegionCode()));
				}
				
				if(u.getCountryProCode() != 0) {
					c.add(Restrictions.eq("countryProCode", u.getCountryProCode()));
				}
				
				if(u.getCountryCityCode() != 0) {
					c.add(Restrictions.eq("countryCityCode", u.getCountryCityCode()));
				}
				
				if(u.getCountryRegCode() != 0) {
					c.add(Restrictions.eq("countryRegCode", u.getCountryRegCode()));
				}
				
				
		        return c.uniqueResult();
			}
		});
		
	}

	public List findAll() {
		
		return this.getHibernateTemplate().find("from User");
	}
}
